﻿using CustomerScoreManagement.Api.Models;
using Dapper;
using Microsoft.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace CustomerScoreManagement.Api.Data
{
    public class DatabaseContext
    {
        private readonly string _connectionString;

        public DatabaseContext(string connectionString)
        {
            _connectionString = connectionString;
        }

        public IDbConnection CreateConnection()
        {
            return new SqlConnection(_connectionString);
        }

        public void UpdateCustomerScore(long customerId, decimal scoreChange)
        {
            using (var connection = CreateConnection())
            {
                var sql = "MERGE INTO Customers AS target " +
                          "USING (SELECT @CustomerId AS CustomerID) AS source " +
                          "ON target.CustomerID = source.CustomerID " +
                          "WHEN MATCHED THEN " +
                          "  UPDATE SET Score = Score + @ScoreChange " +
                          "WHEN NOT MATCHED THEN " +
                          "  INSERT (CustomerID, Score) VALUES (@CustomerId, @ScoreChange);";

                connection.Execute(sql, new { CustomerId = customerId, ScoreChange = scoreChange });
            }
        }

        public IEnumerable<Customer> GetLeaderboard(int start, int end)
        {
            using (var connection = CreateConnection())
            {
                var sql = "SELECT CustomerID, Score FROM Customers WHERE Score > 0 " +
                          "ORDER BY Score DESC, CustomerID " +
                          "OFFSET @Start ROWS FETCH NEXT @End ROWS ONLY;";
                return connection.Query<Customer>(sql, new { Start = start - 1, End = end - start + 1 }).ToList();
            }
        }

        public IEnumerable<Customer> GetNeighbors(long customerId, int high, int low)
        {
            using (var connection = CreateConnection())
            {
                var sql = "WITH RankedCustomers AS (" +
                          "SELECT CustomerID, Score, " +
                          "ROW_NUMBER() OVER (ORDER BY Score DESC, CustomerID) AS Rank " +
                          "FROM Customers WHERE Score > 0) " +
                          "SELECT * FROM RankedCustomers " +
                          "WHERE Rank BETWEEN (SELECT Rank FROM RankedCustomers WHERE CustomerID = @CustomerId) - @High " +
                          "AND (SELECT Rank FROM RankedCustomers WHERE CustomerID = @CustomerId) + @Low;";

                return connection.Query<Customer>(sql, new { CustomerId = customerId, High = high, Low = low }).ToList();
            }
        }
    }
}
